


			**This file collapses the data**	

			**	**	**	**	**	**	**	**	**	**	**	**	**
			**	**	**	**	**	**	**	**	**	**	**	**	**
			**	**	**	**	**	**	**	**	**	**	**	**	**
	


*-------------------------------------------------------------------------------
* Start with removals data
*-------------------------------------------------------------------------------

use "scremovals9-15.dta", clear

*-------------------------------------------------------------------------------
* Drop nonmatching counties
*-------------------------------------------------------------------------------
drop if _m == 1
drop _m

*confirm how many counties we have
count if statecountytag == 1
codebook Id2

**identify the month of first removal for each county
bysort state_county: egen firstmonth = min(month)
format firstmonth %tm

*state county variable
egen statecounty = group(state_county)
xtset statecounty

*CAP county monthly total
bysort state county month: egen temp = sum(count) if capcount == 1
bysort state county month: egen capcountymonthlytotal = mean(temp)
drop temp


*-------------------------------------------------------------------------------
* Create Violent Crime Indicator
*-------------------------------------------------------------------------------

*lower case crimes
replace most_serious_criminal_conviction = lower(most_serious_criminal_conviction)

gen violent = 1 if strpos(most_serious_criminal_conviction, "aggravated assault") > 0
replace violent = 1 if strpos(most_serious_criminal_conviction, "sex assault") > 0
replace violent = 1 if strpos(most_serious_criminal_conviction, "rape") > 0
replace violent = 1 if strpos(most_serious_criminal_conviction, "carjacking") > 0
replace violent = 1 if strpos(most_serious_criminal_conviction, "arson") > 0
replace violent = 1 if strpos(most_serious_criminal_conviction, "homicide") > 0
replace violent = 1 if strpos(most_serious_criminal_conviction, "kidnap") > 0
replace violent = 1 if strpos(most_serious_criminal_conviction, "lewd or lascivious acts") > 0
replace violent = 1 if strpos(most_serious_criminal_conviction, "molestation of minor") > 0
replace violent = 1 if strpos(most_serious_criminal_conviction, "robbery") > 0
replace violent = 1 if strpos(most_serious_criminal_conviction, "sex offense") > 0
replace violent = 1 if strpos(most_serious_criminal_conviction, "sexual assault") > 0
replace violent = 1 if strpos(most_serious_criminal_conviction, "sexual exploitation") > 0
replace violent = 1 if strpos(most_serious_criminal_conviction, "terror") > 0
replace violent = 1 if strpos(most_serious_criminal_conviction, "manslaughter") > 0

*-------------------------------------------------------------------------------
* Create Indicators for Collapse and Checking
*-------------------------------------------------------------------------------


gen threat1 = (rc_threat == "1")
gen threat0 = (rc_threat == "NA")
gen capthreat1 = (rc_threat == "1" & capcount == 1)
gen capthreat0 = (rc_threat == "NA" & capcount == 1)
gen capviolent = (violent == 1 & capcount == 1)

bysort state county month: egen violentcount = sum(violent)

gen atlarge = (latest_apprehension_method == "located" | latest_apprehension_method == "ERO Reprocessed Arrest" | ///
latest_apprehension_method == "Non-Custodial Arrest" | latest_apprehension_method == "Other efforts" | ///
latest_apprehension_method == "Inspections" | latest_apprehension_method == "Patrol Interior" | ///
latest_apprehension_method == "Other Task Force" |  latest_apprehension_method == "Transportation Check Aircraft" | ///
latest_apprehension_method == "Transportation Check Bus" |  latest_apprehension_method == "Transportation Check Freight Train" | ///
latest_apprehension_method == "Transportation Check Passenger Train" |  latest_apprehension_method == "Worksite Enforcement" | ///
latest_apprehension_method == "Traffic Check" )



*-------------------------------------------------------------------------------
* Collapse
*-------------------------------------------------------------------------------


collapse (first) firstmonth state county policymonth census2010pop  ///
 numberhispanic countymonthlytotal capcountymonthlytotal ///
  violentcount Id2 ///
(sum) count threat1 threat0 capthreat1 capthreat0 capviolent violent atlarge ///
, by(statecounty month)

**checks
assert count == countymonthlytotal
assert violent == violentcount
drop count violent
rename threat1 threat1count
rename threat0 threat0count

*fill out dataset
xtset statecounty month
tsfill , full

*create first month variable
bysort statecounty : egen temp = mean(firstmonth)
format temp %tm
replace firstmonth = temp
drop temp

*drop earlier months
drop if month < firstmonth

*need to add names and months back in
gsort + statecounty - state 
by statecounty: carryforward state county census2010pop ///
numberhispanic policymonth Id2, replace

*no missing states or counties now
assert state != ""
assert county != ""

*make missing values zeros
foreach var of varlist countymonthlytotal - violentcount ///
threat1count - atlarge {
replace `var' = 0 if `var' == . 
}

*check that values make sense
assert countymonthly >= capcount
assert countymonthly >= threat1


*-------------------------------------------------------------------------------
* Preparations
*-------------------------------------------------------------------------------

*logs
foreach var of varlist countymonthlytotal threat1count threat0count  violentcount ///
capcountymonthlytotal capthreat1 capthreat0 capviolent{
gen log`var' = ln(`var' + 1)
}

**create nontimevarying sanctuary variable
gen sanctuary = (policymonth < m(2016m1)) 

**no missing in the sample we're using
assert sanctuary < . 

*now create time varying variable
bysort state county: gen monthlysanctuary = (month >= policymonth)
assert monthlysanctuary == 0 if sanctuary == 0


save removalcounts, replace










